﻿using Blm.Utils.Extensions;
using Npgsql;
using System;
using System.Collections.Generic;
using System.Data;
using VisionCloud.Utility.Security;

namespace VisionCloud.Utility.Helpers
{
    public class PostgreHelper
    {
        public static readonly string PgConnStr = AesCoreHelper.Decrypt(ConfigHelper.GetValue<string>("PgsqlSetting:ConnectionMaster"), AesCoreHelper.Sceret);

        /// <summary>
        /// 添加数据集到指定表，dtNew中的列为完整的destTableName表字段，第一列默认为主键
        /// </summary>
        /// <param name="destTableName">目标表</param>
        /// <param name="needToSaveDataTable">要添加到目标表的数据</param>
        /// <returns></returns>
        public static int AddDataSetToDbTableAndUpdate(
            string destTableName,
            string whereToOpe,
            DataTable needToSaveDataTable,
            Action<DataRow, DataTable, DataRow, DataRow[], DataColumn> updateAction,
            Func<DataTable, DataRow, DataRow[]> GetCurrentDbRow)
        {
            if (needToSaveDataTable == null || needToSaveDataTable.Rows.Count == 0)
            {
                return -1;
            }

            if (string.IsNullOrEmpty(whereToOpe))
            {
                whereToOpe = "1 = 2";
            }
            //获取数据库里面的数据
            string strSql = $"select * from \"{destTableName}\" where {whereToOpe} ";
            using NpgsqlConnection connection = new NpgsqlConnection(PgConnStr);
            connection.Open();
            using NpgsqlDataAdapter da = new NpgsqlDataAdapter(strSql, connection);
            using DataSet _dataSet = new DataSet();
            da.Fill(_dataSet);
            var _dbTable = _dataSet.Tables[0];

            using NpgsqlCommandBuilder cmdb = new NpgsqlCommandBuilder(da);
            da.InsertCommand = cmdb.GetInsertCommand();
            da.UpdateCommand = cmdb.GetUpdateCommand();
            da.DeleteCommand = cmdb.GetDeleteCommand();

            //循环当前传进来的dataTable
            foreach (DataRow needToSaveRow in needToSaveDataTable.Rows)
            {
                //已经数据库为模板创建新的一行
                DataRow _newDataRow = _dbTable.NewRow();
                //更加主键获取当前行的记录
                if (GetCurrentDbRow != null)
                {
                    var _currentDbRowArray = GetCurrentDbRow(_dbTable, needToSaveRow); //_dbTable.Select($"{primaryKey[0].ColumnName}='{rowKeyValue}'");
                    if (_currentDbRowArray.Length == 1)
                    {
                        //把数据库中的行赋值给新行，数据库的行删除
                        _newDataRow.ItemArray = _currentDbRowArray[0].ItemArray;
                        _currentDbRowArray[0].Delete();
                    }
                    if (updateAction != null)
                    {
                        //循环表中的列
                        foreach (DataColumn _dbTableColumn in _dbTable.Columns)
                        {
                            updateAction(_newDataRow, needToSaveDataTable, needToSaveRow, _currentDbRowArray, _dbTableColumn);
                        }
                    }
                    //把创建的新行赋值到dataSet中
                    _dataSet.Tables[0].Rows.Add(_newDataRow);
                }
                //  var rowKeyValue = currentNewDataTableRow[primaryKey[0].ColumnName];
                //从数据库表中选择
            }
            //更新数据
            int i = da.Update(_dataSet);
            _dataSet.AcceptChanges();
            return i;
        }

        /// <summary>
        /// 添加数据集到指定表，dtNew中的列为完整的destTableName表字段，第一列默认为主键
        /// </summary>
        /// <param name="destTableName">目标表</param>
        /// <param name="dtNew">要添加到目标表的数据</param>
        /// <param name="primaryKey">不传入，默认为第一列</param>
        /// <param name="keepOldData">只支持单主键：保留旧数据，则新数据没有</param>
        /// <returns></returns>
        public static int AddDataSetToDbTable(string destTableName, string whereToOpe, DataTable dtNew, DataColumn[] primaryKey = null, bool keepOldData = false, Func<DataRow, string> getRowByKey = null)
        {

            if (dtNew == null || dtNew.Rows.Count == 0)
            {
                return -1;
            }

            if (string.IsNullOrEmpty(whereToOpe))
            {
                whereToOpe = " 1= 2";
            }

            string strSql = $"select * from \"{destTableName}\" where {whereToOpe} ";
            using NpgsqlConnection connection = new NpgsqlConnection(PgConnStr);
            connection.Open();
            using NpgsqlDataAdapter da = new NpgsqlDataAdapter(strSql, connection);
            using DataSet ds = new DataSet();
            da.Fill(ds);
            var destDt = ds.Tables[0];

            using NpgsqlCommandBuilder cmdb = new NpgsqlCommandBuilder(da);
            da.InsertCommand = cmdb.GetInsertCommand();
            da.UpdateCommand = cmdb.GetUpdateCommand();
            da.DeleteCommand = cmdb.GetDeleteCommand();

            if (primaryKey != null)
            {
                List<DataColumn> dcList = new List<DataColumn>();
                foreach (DataColumn dc in primaryKey)
                {
                    if (destDt.Columns.Contains(dc.ColumnName))
                    {
                        dcList.Add(destDt.Columns[dc.ColumnName]);
                    }
                }

                destDt.PrimaryKey = dcList.ToArray();
            }
            else
            {
                destDt.PrimaryKey = new DataColumn[] { ds.Tables[0].Columns[0] };
                // 默认采用第一列作为主键
                primaryKey = destDt.PrimaryKey;
            }

            if (destTableName == "MsFileStatus")
            {
                Logger.Info(string.Format("AddDataSetToDbTable需要更新的数据集为{0}", ds.ToJsonStr()));
            }

            foreach (DataRow dr in dtNew.Rows)
            {
                DataRow drNew = destDt.NewRow();
                DataRow[] drs;
                if (getRowByKey == null)
                {
                    var rowKeyValue = dr[primaryKey[0]?.ColumnName];
                    drs = destDt.Select($"{primaryKey[0].ColumnName}='{rowKeyValue}'");
                }
                else
                {
                    drs = destDt.Select(getRowByKey(dr));
                }
                if (drs?.Length == 1)
                {
                    drNew.ItemArray = drs[0].ItemArray;
                    drs[0].Delete();
                }

                foreach (DataColumn dc in destDt.Columns)
                {
                    if (dtNew.Columns.Contains(dc.ColumnName))
                    {
                        drNew[dc.ColumnName] = dr[dc.ColumnName];
                    }
                }

                ds.Tables[0].Rows.Add(drNew);
            }
            int i = da.Update(ds);
            ds.AcceptChanges();
            return i;
        }

        /// <summary>
        /// 添加数据集到指定表，dtNew中的列为完整的destTableName表字段，第一列默认为主键
        /// </summary>
        /// <param name="destTableName">目标表</param>
        /// <param name="dtNew">要添加到目标表的数据</param>
        /// <param name="primaryKey">不传入，默认为第一列</param>
        /// <param name="keepOldData">只支持单主键：保留旧数据，则新数据没有</param>
        /// <returns></returns>
        public int AddDataSetToDbTable(string destTableName, string whereToOpe, DataTable dtNew, DataColumn[] primaryKey = null, bool keepOldData = false)
        {
            if (dtNew == null || dtNew.Rows.Count == 0)
            {
                return -1;
            }

            if (string.IsNullOrEmpty(whereToOpe))
            {
                whereToOpe = "1=2";
            }
            string strSql = $"select * from \"{destTableName}\" where {whereToOpe} ";
            using (NpgsqlConnection connection = new NpgsqlConnection(PgConnStr))
            {
                connection.Open();
                using (NpgsqlDataAdapter da = new NpgsqlDataAdapter(strSql, connection))
                {
                    using (DataSet ds = new DataSet())
                    {
                        da.Fill(ds);
                        var destDt = ds.Tables[0];
                        if (primaryKey != null)
                        {
                            List<DataColumn> dcList = new List<DataColumn>();
                            foreach (DataColumn dc in primaryKey)
                            {
                                if (destDt.Columns.Contains(dc.ColumnName))
                                {
                                    dcList.Add(destDt.Columns[dc.ColumnName]);
                                }
                            }

                            destDt.PrimaryKey = dcList.ToArray();
                        }
                        else
                        {
                            destDt.PrimaryKey = new DataColumn[] { ds.Tables[0].Columns[0] };
                            // 默认采用第一列作为主键
                            primaryKey = destDt.PrimaryKey;
                        }

                        using (NpgsqlCommandBuilder cmdb = new NpgsqlCommandBuilder(da))
                        {
                            da.InsertCommand = cmdb.GetInsertCommand();
                            da.UpdateCommand = cmdb.GetUpdateCommand();
                            da.DeleteCommand = cmdb.GetDeleteCommand();
                            foreach (DataRow dr in dtNew.Rows)
                            {
                                DataRow drNew = destDt.NewRow();

                                var rowKeyValue = dr[primaryKey[0]?.ColumnName];
                                var drs = destDt.Select($"{primaryKey[0].ColumnName}='{rowKeyValue}'");
                                if (drs.Length == 1)
                                {
                                    drNew.ItemArray = drs[0].ItemArray;
                                    drs[0].Delete();
                                }

                                //drNew.ItemArray = dr.ItemArray;
                                foreach (DataColumn dc in destDt.Columns)
                                {
                                    //if (dc.ColumnName == "JsonSourceFilePath" || dc.ColumnName == "JsonResultFilePath")
                                    //{
                                    //    dc.DataType = typeof(json);
                                    //}
                                    if (dtNew.Columns.Contains(dc.ColumnName))
                                    {
                                        drNew[dc.ColumnName] = dr[dc.ColumnName];
                                    }
                                    //[Column(TypeName = "json")]
                                }

                                ds.Tables[0].Rows.Add(drNew);
                            }

                            //foreach (DataRow dr in destDt.Rows)
                            //{
                            //    dr.Delete();
                            //}
                            int i = da.Update(ds);
                            ds.AcceptChanges();
                            return i;
                        }
                    }
                }
            }
        }

        /// <summary>
        /// 读取数据，输出成DataSet
        /// </summary>
        /// <param name="sqlstr"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        [Obsolete("该方法废旧，请使用PDBHelper替代")]
        public DataSet ReadDataSet(string sqlstr, NpgsqlParameter[] parameters = null)
        {
            NpgsqlCommand cmd = null;
            try
            {
                using (NpgsqlConnection connection = new NpgsqlConnection(PgConnStr))
                {
                    using (cmd = new NpgsqlCommand(sqlstr, connection))
                    {
                        if (parameters != null)
                        {
                            foreach (NpgsqlParameter parameter in parameters)
                            {
                                cmd.Parameters.Add(parameter);
                            }
                        }
                        using (NpgsqlDataAdapter dataAdapter = new NpgsqlDataAdapter(cmd))
                        {
                            DataSet ds = new DataSet();
                            dataAdapter.Fill(ds);
                            cmd.Parameters.Clear();
                            return ds;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Logger.Error($"Error in PostgreHelper.ReadDataSet:{ex.Message}");
                return null;
            }
            finally
            {
                if (cmd != null)
                {
                    cmd.Parameters.Clear();
                }
            }
        }
    }
}